﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace CJJ_BlogApiV3.Utils.Tools.Excel
{
    /// <summary>
    /// 操作EXCEL
    /// </summary>
    public class ExcelHelper
    {
        private IWorkbook workbook = null;
        private Stream steam = null;
        private bool disposed;

        /// <summary>
        /// 函数
        /// </summary>
        public ExcelHelper()
        {
            disposed = false;
        }

        #region 读取Excel
        /// <summary>
        /// Excel字节转DataTable
        /// </summary>
        /// <param name="steam">字节</param>
        /// <param name="fileName">文件名</param>
        /// <returns>结果</returns>
        public DataTable ExcelToDataTable(Stream steam, string fileName)
        {
            DataTable data = new DataTable();
            string sheetName = "Sheet1";
            try
            {
                if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
                    workbook = new XSSFWorkbook(steam);
                else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
                    workbook = new HSSFWorkbook(steam);

                var sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            var cellValue = GetCellValue(cell);
                            if (cellValue != null)
                            {
                                DataColumn column = new DataColumn(cellValue.ToString());
                                data.Columns.Add(column);
                            }
                        }
                    }
                    var startRow = sheet.FirstRowNum + 1;
                    // startRow = sheet.FirstRowNum;


                    //最后一行的标号
                    int rowCount = sheet.LastRowNum;
                    cellCount = data.Columns.Count;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　

                        bool flg = false;
                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            var val = GetCellValue(row.GetCell(j));
                            if (val != null)
                            {
                                flg = true;
                            }
                            dataRow[j] = val;
                        }
                        if (!flg) continue;
                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 根据单元格的类型获取对应的值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>单元格的值</returns>
        private object GetCellValue(ICell cell)
        {
            object value = null;
            try
            {
                if (cell.CellType != CellType.Blank)
                {
                    switch (cell.CellType)
                    {
                        case CellType.Numeric:
                            // Date comes here
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                value = cell.DateCellValue;
                            }
                            else
                            {
                                // Numeric type
                                value = cell.NumericCellValue;
                            }
                            break;
                        case CellType.Boolean:
                            // Boolean type
                            value = cell.BooleanCellValue;
                            break;
                        case CellType.Formula:
                            value = cell.CellFormula;
                            break;
                        default:
                            // String type
                            value = cell.StringCellValue;
                            break;
                    }
                }
            }
            catch (Exception)
            {
                value = null;
            }

            return value;
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        /// <param name="disposing"></param>
        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (steam != null)
                        steam.Close();
                }

                steam = null;
                disposed = true;
            }
        }
        #endregion


        #region 生成Excel

        /// <summary>
        /// 生成Excel
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream BuildToExcel(DataTable table)
        {
            MemoryStream ms = new MemoryStream();

            using (table)
            {
                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow(0);

                // handling header.
                foreach (DataColumn column in table.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
                }

                // handling value.
                int rowIndex = 1;

                foreach (DataRow row in table.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in table.Columns)
                    {
                        decimal num;
                        if (decimal.TryParse(row[column].ToString(), out num) == false || row[column].ToString().Length >= 10)
                        {
                            dataRow.CreateCell(column.Ordinal, CellType.String).SetCellValue(row[column].ToString());
                        }
                        else
                        {
                            ICell cell = dataRow.CreateCell(column.Ordinal, CellType.Numeric);
                            cell.SetCellValue(Convert.ToDouble(row[column].ToString()));
                        }

                    }

                    rowIndex++;
                }

                AutoSizeColumns(sheet);
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;


            }

            return ms;
        }

        /// <summary>
        /// 生成Excel 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="colorDic">颜色值</param>
        /// <returns></returns>
        public static MemoryStream BuildToExcel(DataTable table, Dictionary<string, int> colorDic)
        {
            MemoryStream ms = new MemoryStream();

            using (table)
            {
                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow(0);

                foreach (DataColumn column in table.Columns)
                {
                    var row = headerRow.CreateCell(column.Ordinal);
                    row.SetCellValue(column.Caption);

                    if (colorDic != null && colorDic.Count > 0)
                    {
                        if (colorDic.ContainsKey(column.Caption))
                        {
                            //获取颜色
                            //int colorIndex = colorDic[column.Caption];

                            ICellStyle style = workbook.CreateCellStyle();//创建样式对象
                            IFont font = workbook.CreateFont(); //创建一个字体样式对象
                            font.Color = new HSSFColor.Red().Indexed; //颜色参考NPOI的颜色对照表(替换掉PINK())
                            style.SetFont(font);
                            row.CellStyle = style;
                        }
                    }
                }

                // handling value.
                int rowIndex = 1;

                foreach (DataRow row in table.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in table.Columns)
                    {
                        decimal num;
                        if (decimal.TryParse(row[column].ToString(), out num) == false || row[column].ToString().Length >= 10)
                        {
                            dataRow.CreateCell(column.Ordinal, CellType.String).SetCellValue(row[column].ToString());
                        }
                        else
                        {
                            ICell cell = dataRow.CreateCell(column.Ordinal, CellType.Numeric);
                            cell.SetCellValue(Convert.ToDouble(row[column].ToString()));

                        }

                    }

                    rowIndex++;
                }

                AutoSizeColumns(sheet);

                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;


            }

            return ms;
        }

        /// <summary>
        /// 生成CSV
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream BuildToCsv(DataTable table)
        {
            Encoding encode = Encoding.GetEncoding("gb2312");
            StringBuilder str = new StringBuilder();
            if (table != null && table.Columns.Count > 0 && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    str.Append(table.Columns[i].ColumnName.Replace("\"", "\"\""));
                    if (i < table.Columns.Count - 1)
                    {
                        str.Append(",");
                    }
                }

                foreach (DataRow item in table.Rows)
                {
                    str.Append("\r\n");
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        if (item[i] != null)
                        {
                            str.Append("'").Append(item[i].ToString().Replace("\"", "\"\""));
                        }

                        if (i < table.Columns.Count - 1)
                        {
                            str.Append(",");
                        }
                    }
                }
            }

            MemoryStream stream = new MemoryStream(encode.GetBytes(str.ToString()));
            return stream;
        }

        /// <summary>
        /// 自动设置Excel列宽
        /// </summary>
        /// <param name="sheet">Excel表</param>
        private static void AutoSizeColumns(ISheet sheet)
        {
            if (sheet.PhysicalNumberOfRows > 0)
            {
                IRow headerRow = sheet.GetRow(0);

                for (int i = 0, l = headerRow.LastCellNum; i < l; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
            }
        }

        /// <summary>
        /// 生成Excel
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="isToText">是否转换成文本类型保存</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream BuildToExcel(DataTable table, bool isToText)
        {
            MemoryStream ms = new MemoryStream();

            using (table)
            {
                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow(0);

                // handling header.
                foreach (DataColumn column in table.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
                }

                // handling value.
                int rowIndex = 1;

                foreach (DataRow row in table.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in table.Columns)
                    {
                        decimal num = 0;
                        if (decimal.TryParse(row[column].ToString(), out num) == false || isToText)
                        {
                            dataRow.CreateCell(column.Ordinal, CellType.String).SetCellValue(row[column].ToString());
                        }
                        else
                        {
                            ICell cell = dataRow.CreateCell(column.Ordinal, CellType.Numeric);
                            cell.SetCellValue(Convert.ToDouble(row[column].ToString()));
                        }

                    }

                    rowIndex++;
                }

                AutoSizeColumns(sheet);
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
            }

            return ms;
        }
        #endregion

        #region
        /// <summary>
        /// 根据索引获取颜色值
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        private static short GetColorByIndex(int index)
        {
            short color = 0;
            if (index == 1)
                color = new HSSFColor.Red().Indexed;

            return color;
        }
        #endregion
    }
}
